#!/usr/bin/env python
# -*- coding: utf-8 -*-
import win32com.client


# ---------------------------------------------------------------------------
class easyExcel:
    '''
    Some convenience methods for Excel documents accessed
    through COM.
    '''

    def __init__(self, filename=None):
        '''
        Create a new application
        if filename is None, create a new file
        else, open an exsiting one
        '''
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        self.xlApp.Visible = False
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def visible(self, visible=True):
        '''
        if Visible is true, the applicaion is visible
        '''
        self.Visible = visible

    def save(self, newfilename=None):
        '''
        if filename is None, save the openning file
        else save as another file used the given name
        '''
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):
        '''
        Close the application
        '''
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp

    def getCell(self, sheet, row, col):
        '''
        Get value of one cell
        '''
        sht = self.xlBook.Worksheets(sheet)
        return sht.Cells(row, col).Value

    def setCell(self, sheet, row, col, value):
        '''
        Set value of one cell
        '''
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Value = value

    def getRange(self, sheet, row1, col1, row2, col2):
        '''
        Return a 2d array (i.e. tuple of tuples)
        '''
        sht = self.xlBook.Worksheets(sheet)
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

    def setRange(self, sheet, leftCol, topRow, data):
        '''
        Insert a 2d array starting at given location.
        i.e. [['a','b','c'],['a','b','c'],['a','b','c']]
        Works out the size needed for itself
        '''
        bottomRow = topRow + len(data) - 1
        rightCol = leftCol + len(data[0]) - 1
        sht = self.xlBook.Worksheets(sheet)
        sht.Range(sht.Cells(topRow, leftCol), sht.Cells(bottomRow, rightCol)).Value = data


if __name__ == "__main__":
    excelProxy = easyExcel("D:\chart_demo.xls")
    content = excelProxy.getRange("sheet1", 4, 1, 6, 3)
    print(content)
    #    excelProxy.setRange("sheet1",2,1, ['a','b','c'])
    #    excelProxy.save()
    excelProxy.close()